package js;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.RandomAccessFile;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import bean.SmsMgrInfo;
import deal.DealDataOfHis;
import deal.GetDataOfHis;
import deal.StopDataOfHis;
import util.AES;
import util.CSVUtils;
import util.ConfigUtils;
import util.ConnectionManager;
import util.DbUtils;


public class CsvExport {
	private static Logger logger=LogManager.getLogger(CsvExport.class);

	static Date d = new Date();
	static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
	static String dateNowStr = sdf.format(d);

	private static final String File_PATH = ConfigUtils.getInstance().getValue(
			"localbasePath");// �ļ����մ��Ŀ¼
	private static final String File_NAME = ConfigUtils.getInstance().getValue(
			"filename");// ����ļ���
	private static final String OK_PATH = ConfigUtils.getInstance().getValue(
			"localbaseOkPath");//ok�ļ����Ŀ¼
	private static final String EXPORT_FLAG = ConfigUtils.getInstance().getValue(
            "exportFlag");//��������
	public static int count = 0;
	public synchronized static void increase() {
		CsvExport.count++;
	}
	public static long startTime;
	public static boolean flag = false;
	public static Integer Max = 100;//һ��csv����ŵ�������
	public static Map<String, Integer> csvCount = new ConcurrentHashMap<String, Integer>();
	public static boolean exit = false;

	public synchronized static void createNewCsv(String csvName,String numString,Map<String, Object> dataMap,String filePath) {
		//����ļ���û�д����򴴽����ļ�����������
		CsvExport.csvCount.put(csvName+"_"+numString, 1);
		List<Object> head=new ArrayList<Object>();
		List<List<Object>> dataList=new ArrayList<List<Object>>();
		List<Object> date=new ArrayList<Object>();
		for(Object key:dataMap.keySet()){
			if (!"ID".equals(String.valueOf(key))) {
				date.add(dataMap.get(key));
			}
		}

		date.add(1);

		dataList.add(date);
		CSVUtils.createCSVFile(head,dataList,File_PATH,filePath+"_"+numString);
	}

	public synchronized static void createNewCsv2(String csvName,Map<String, Object> dataMap,String filePath) {
		//��¼���ļ��ж���������
		List<Object> head=new ArrayList<Object>();
		List<List<Object>> dataList=new ArrayList<List<Object>>();
		List<Object> date=new ArrayList<Object>();
		for(Object key:dataMap.keySet()){
			if (!"ID".equals(String.valueOf(key))) {
				date.add(dataMap.get(key));
			}
		}


		dataList.add(date);
		CSVUtils.createCSVFile(head,dataList,File_PATH,filePath);
	}

	public synchronized static void writeCsv(String testString,String filePath) {
		File csv = new File(File_PATH + File.separator + filePath + ".csv"); // CSV�����ļ�
	    BufferedWriter bw;
		try {
			bw = new BufferedWriter(new FileWriter(csv, true));
		    bw.write(testString);
		    bw.newLine();
		    bw.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * ����csv
	 * @Description:
	 * @author liurr
	 * @date 2018��5��3�� ����3:26:41
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		startTime=System.currentTimeMillis();
		logger.info("************��ʼ�����ݿ����ӳ�************");
		ConnectionManager.getInstance();
		logger.info("************��ȡ��վ��Ϣ************");
		//�����߳����ڻ�ȡ��վ��Ϣ
		GetDataOfHis getDataOfHis = new GetDataOfHis();
		Thread getDataThread = new Thread(getDataOfHis);
		getDataThread.start();
		logger.info("************����csv�ļ�************");
		//�����߳����ڵ���csv�ļ�
		for (int i = 0; i < 10; i++) {
			DealDataOfHis dealDataOfHis = new DealDataOfHis(i);
			Thread dealDataThread = new Thread(dealDataOfHis);
			dealDataThread.start();
		}

		StopDataOfHis stopDataOfHis = new StopDataOfHis();
		Thread stopDataThread = new Thread(stopDataOfHis);
		stopDataThread.start();

	}
	/**
	 * ��ѯ����Ҫ��������Ŀ����Ϣ
	 *
	 * @return List<SmsMgrInfo>
	 */
	public List<SmsMgrInfo> findMgrList() throws Exception{
		logger.debug("��ʼ�����ļ�������־��");
		Connection conn = null;
		PreparedStatement pres = null;
		ResultSet rs = null;
		List<SmsMgrInfo> list = new ArrayList<SmsMgrInfo>();
		logger.info("****************�����ļ���û����Ŀ������Ϣ����ʼ��ѯ��Ŀ��Ϣ****************");
		String sql = "select distinct city_id,id from sms_mgr_info";
		try {
			conn = ConnectionManager.getInstance().getConnection();
			pres = conn.prepareStatement(sql);
			rs = pres.executeQuery();
			while (rs.next()) {
				SmsMgrInfo mgrInfo = new SmsMgrInfo();
				mgrInfo.setCityId(rs.getString(1));
				mgrInfo.setId(rs.getString(2));
				list.add(mgrInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			//SendMsg.sendWarnSms("����1:��ȡ��Ŀ��Ϣʧ��");
		} finally {
			DbUtils.closeQuietly(rs, pres, conn);
		}
		logger.info("��ȡ��Ŀ�б����");
		return list;
	}
	public List<Map<String,Object>> getFile() throws Exception{
		int count = 0;
		Connection conn = null;
//        PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		String sql = "";
		/*sql =  "select t.phone_number,  "
				+"t.station_id, "
				+"     t.city_id, "
				+"      to_char(t.enter_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     to_char(t.receive_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     t.user_place, "
				+"     t.user_city, "
				+"     t.Is_Local, "
				+"     t.is_cz, "
				+"     to_char(t.operator_time, 'yyyy/mm/dd hh24:mi:ss') "
				+"  from his_base_target_users t,sms_base_station s "
				+ " where t.station_id=s.station_id and s.id="+param
				+ " and to_char(t.operator_time, 'yyyy/mm/dd') = to_char(sysdate-1, 'yyyy/mm/dd') "
				;*/
		sql =  "select t.phone_number,  "
				+"t.station_id, "
				+"     t.city_id, "
				+"      to_char(t.enter_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     to_char(t.receive_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     t.user_place, "
				+"     t.user_city, "
				+"     t.Is_Local, "
				+"     t.is_cz, "
				+"     to_char(t.operator_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+" 	   s.id"
				+"  from his_base_target_users t,sms_base_station s "
				+ " where t.station_id=s.station_id "
				//+ " and to_char(t.operator_time, 'yyyy/mm/dd') = to_char(sysdate-1, 'yyyy/mm/dd') "
				;
		try {
			conn = ConnectionManager.getInstance().getConnection();
//            pstmt = conn.prepareStatement(sql);
//            rs = pstmt.executeQuery();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(300);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData(); //��ý�����ṹ��Ϣ,Ԫ����
			int columnCount = md.getColumnCount();   //�������
			while (rs.next()) {
				count++;
				Map<String,Object> rowData = new LinkedHashMap<String,Object>();
				for (int i = 1; i <= columnCount; i++) {
					if (i==1) {
						String noPwdMsisdn = AES.decrypt(rs.getObject(i).toString(), "HuaDongJieSuo123");
						rowData.put(md.getColumnName(i),noPwdMsisdn);
					}else {
						rowData.put(md.getColumnName(i), rs.getObject(i));
					}
				}
				list.add(rowData);
				if (count %10000 ==0) {
					logger.info("get data ##"+count);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
					rs = null;
				}
				if (null != stmt) {
					stmt.close();
				}
			} catch (SQLException e) {

				e.printStackTrace();
			}
			DbUtils.closeQuietly(conn);
		}
		return list;
	}
	public static void AssembledData1(List<Map<String,Object>> retList,String mgrId,String cityId) {
		if(retList==null||retList.size()==0){
			logger.info("************���ݿ��վ��ϢΪ��************");
		}else{
			List<Object> head=new ArrayList<Object>();
			List<List<Object>> dataList=new ArrayList<List<Object>>();
			for (Map<String, Object> map : retList) {
				List<Object> date=new ArrayList<Object>();
				for(Object key:map.keySet()){
					date.add(map.get(key));
				}
				dataList.add(date);
			}
			for(Object ob:retList.get(0).keySet()){
				head.add(ob);
			}
			CSVUtils.createCSVFile(head,dataList,File_PATH,cityId+File.separator+mgrId+File.separator+mgrId+"_"+dateNowStr);
		}

	}
	/**
	 * ��ȡ��վ��Ϣ
	 * @Description:
	 * @author liurr
	 * @date 2018��5��3�� ����3:32:38
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> findBaseStationInfo() throws Exception{
		int count = 0;
		Connection conn = null;
//        PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		String sql =  "select t.phone_number,  "
				+"t.station_id, "
				+"     t.city_id, "
				+"      to_char(t.enter_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     to_char(t.receive_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     t.user_place, "
				+"     t.user_city, "
				+"     t.Is_Local, "
				+"     t.is_cz, "
				+"     to_char(t.operator_time, 'yyyy/mm/dd hh24:mi:ss') "
				+"  from his_base_target_users t where to_char(t.operator_time, 'yyyy/mm/dd') = to_char(sysdate-1, 'yyyy/mm/dd') ";

		try {
			conn = ConnectionManager.getInstance().getConnection();
//            pstmt = conn.prepareStatement(sql);
//            rs = pstmt.executeQuery();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(300);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData(); //��ý�����ṹ��Ϣ,Ԫ����
			int columnCount = md.getColumnCount();   //�������
			while (rs.next()) {
				count++;
				Map<String,Object> rowData = new LinkedHashMap<String,Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
				if (count %10000 ==0) {
					logger.info("get data ##"+count);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
					rs = null;
				}
				if (null != stmt) {
					stmt.close();
				}
			} catch (SQLException e) {

				e.printStackTrace();
			}
			DbUtils.closeQuietly(conn);
		}
		return list;
	}

	/**
	 * ��װ����
	 * @Description:
	 * @author liurr
	 * @date 2018��5��3�� ����3:25:57
	 * @param retList
	 */
	public static void AssembledData(List<Map<String,Object>> retList) {
		if(retList==null||retList.size()==0){
			logger.info("************���ݿ��վ��ϢΪ��************");
		}else{
			List<Object> head=new ArrayList<Object>();
			List<List<Object>> dataList=new ArrayList<List<Object>>();
			for (Map<String, Object> map : retList) {
				List<Object> date=new ArrayList<Object>();
				for(Object key:map.keySet()){
					date.add(map.get(key));
				}
				dataList.add(date);
			}
			for(Object ob:retList.get(0).keySet()){
				head.add(ob);
			}
			CSVUtils.createCSVFile(head,dataList,File_PATH,File_NAME);
		}

	}
	/**
	 * д�ļ�
	 * @Description:
	 * @author liurr
	 * @date 2018��8��21�� ����4:36:08
	 * @param newStr
	 * @throws IOException
	 */
	public static void writeTxtFile(String newStr) throws IOException{
		//�ȶ�ȡԭ���ļ�����,Ȼ�����д�����
		String filein = newStr + ".csv"+"\r\n";
		RandomAccessFile mm = null;
		try {
			String path1 = OK_PATH.substring(0,OK_PATH.lastIndexOf('\\'));
			if (!Files.exists(Paths.get(path1))) Files.createDirectories(Paths.get(path1));
			mm = new RandomAccessFile(OK_PATH, "rw");
			mm.writeBytes(filein);
		} catch (IOException e1) {
			e1.printStackTrace();
		} finally {
			if (mm != null) {
				try {
					mm.close();
				} catch (IOException e2) {
					e2.printStackTrace();
				}
			}
		}
	}

	 public static void his() throws SQLException, IOException {
	    	logger.info("start export his data!");
	    	int count = 0;
	    	File filePath = new File(File_PATH);
			File file = new File(File_PATH+File_NAME);
			if ( !filePath.exists()) {
				new File(File_PATH).mkdirs();
				logger.info("create File PATH sucess");
			}
			if (file.exists()) {
				file.delete();
			}
			file.createNewFile();
			logger.info("create New File sucess ");
	    	FileOutputStream fos = new FileOutputStream(file);
			OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
			BufferedWriter bw = new BufferedWriter(osw);
			String sql =  "select t.phone_number,  "
					+"t.station_id, "
					+"     t.city_id, "
					+"      to_char(t.enter_time, 'yyyy/mm/dd hh24:mi:ss'), "
					+"     to_char(t.receive_time, 'yyyy/mm/dd hh24:mi:ss'), "
					+"     t.user_place, "
					+"     t.user_city, "
					+"     t.Is_Local, "
					+"     t.is_cz, "
					+"     to_char(t.operator_time, 'yyyy/mm/dd hh24:mi:ss') "
					+"  from his_base_target_users t where to_char(t.operator_time, 'yyyy/mm/dd') = to_char(sysdate-1, 'yyyy/mm/dd') ";

//					"select t.phone_number,t.mgr_id from seven_users t join sms_template_attr a on t.mgr_id =a.id where ((sysdate -send_time)<a.local_date and t.is_local=0) "
//					+ " or ((sysdate -send_time)<7 and t.is_local=1) ";
			Statement stmt = null;
			ResultSet rs = null;
			Connection conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(500);
//				stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String msisdn = rs.getString("phone_number");
				try{
					String mgrId = rs.getString("mgr_id");

					if (null != msisdn && !"".equals(msisdn)) {
						String noPwdMsisdn = AES.decrypt(msisdn, "HuaDongJieSuo123");
						bw.write(noPwdMsisdn+","+mgrId+"\r\n");
					}
				} catch (Exception e) {
					logger.error("get msisdn error,msisdn={"+msisdn+"}",e);
				}
				count++;
				if (count%5000==0) {
					logger.info("already export data count "+count);
				}
			}

			if (null != bw) {
				bw.close();
			}
			if (null != osw) {
				osw.close();
			}
			if (null != fos) {
				fos.close();
			}
			if (null != rs) {
				rs.close();
			}

			if (null != stmt) {
				stmt.close();
			}
			DbUtils.closeQuietly(null, null, conn);

			logger.info("export his Info data end! export data count "+count);
	    }

}
